const { db, query } = require("../database/db")
// const multer = require('multer'); // 用于处理multipart/form-data


// 添加作业--布置作业
const add = (title, description, class_id, set_date, due_date, difficulty, teacher, picture) => {
    let sql = `
        INSERT INTO assignments (title,description,class_id,set_date,due_date,difficulty,teacher,picture)
        VALUES (?,?,?,?,?,?,?)
    `
    return query(sql, [title, description, class_id, set_date, due_date, difficulty, teacher, picture])
}
// 删除作业--软删除
const del_f = (id) => {
    let sql = `
        UPDATE assignments SET state_del = 1 WHERE id = ?
    `
    return query(sql, id)
}
const start_state = (id) => {
    let sql = `
        UPDATE assignments SET state_del = 0 WHERE id = ?
    `
    return query(sql, id)
}

// 删除作业--真删除
const del_t = (id) => {
    let sql = `
        DELETE FROM assignments WHERE id = ?
    `
    return query(sql, id)
}

// 作业查看-获取数据
const get = (id) => {
    /*
        class_id: 5
        description: "完成课堂练习内容"
        difficulty: "中等"
        due_date: "2025-07-07"
        id: 7
        picture: "public/uploads/1751888088003.jpg"
        set_date: "2025-06-01"
        state_del: 0
        teacher: "海波东"
        teacher_id: 5
        title: "python练习-250601"
    */

    let sql = `
        SELECT 
            assignments.id,assignments.title, assignments.description, assignments.class_id, assignments.set_date,
            assignments.due_date, assignments.difficulty, assignments.picture, assignments.state_del, 
            assignments.teacher_id,classes.name AS class_name, programs.name AS program_name, users.truename as teacherName
        FROM assignments
        JOIN classes ON assignments.class_id = classes.id 
        JOIN programs ON classes.program_id = programs.id
        JOIN users ON assignments.teacher_id = users.id
        WHERE assignments.id = ?
    `
    return query(sql, id)
}

// 作业查看-保存数据
const set = (id, title, description, class_id, set_date, due_date, difficulty, teacher, picture) => {
    let sql = `
        UPDATE assignments SET title=?, description=?, class_id=?, set_date=?, due_date=?, difficulty=?, teacher=?, picture=?
        WHERE id = ?
    `
    return query(sql, [title, description, class_id, set_date, due_date, difficulty, teacher, picture, id])
}

// 作业列表搜索
const list = async (title, class_id, teacher_id, program_id, difficulty, set_date_start, set_date_end, page_size, page) => {
    // 基础SQL查询语句，使用JOIN连接assignments表、classes表和programs表
    let listSql = `
        SELECT 
            assignments.id,assignments.title, assignments.description, assignments.class_id, assignments.set_date,
            assignments.due_date, assignments.difficulty, assignments.picture, assignments.state_del, 
            assignments.teacher_id,classes.name AS class_name, programs.name AS program_name, users.truename as teacherName
        FROM assignments
        JOIN classes ON assignments.class_id = classes.id 
        JOIN programs ON classes.program_id = programs.id
        JOIN users ON assignments.teacher_id = users.id
    `;
    // 初始化参数数组
    let params = [];

    // 如果有搜索条件，添加到SQL语句中
    let conditions = [`0 = 0`]; // 初始化条件数组
    // 根据前端的请求数据加条件
    if (title) {
        conditions.push(`assignments.title LIKE ?`);
        params.push(`%${title}%`);
    }
    if (class_id) {
        conditions.push(`assignments.class_id = ?`);
        params.push(class_id);
    }

    if (teacher_id) {
        conditions.push(`assignments.teacher_id = ?`);
        params.push(teacher_id);
    }

    if (program_id) {
        conditions.push(`programs.id = ?`);
        params.push(program_id);
    }
    if (difficulty) {
        conditions.push(`assignments.difficulty = ?`);
        params.push(difficulty);
    }

    //  set_date_start, set_date_end
    if (set_date_start && set_date_end) {
        // 当同时提供开始日期和结束日期时，查询日期范围内的数据
        conditions.push(`assignments.set_date BETWEEN ? AND ?`);
        params.push(set_date_start, set_date_end);
    } else if (set_date_start) {
        // 仅提供开始日期时，查询该日期之后的数据
        conditions.push(`assignments.set_date >= ?`);
        params.push(set_date_start);
    } else if (set_date_end) {
        // 仅提供结束日期时，查询该日期之前的数据
        conditions.push(`assignments.set_date <= ?`);
        params.push(set_date_end);
    }

    // 如果有搜索条件，添加WHERE子句
    if (conditions.length > 1) {
        listSql += ` WHERE ${conditions.join(' AND ')}`;
    }

    // 添加分页的SQL语句
    listSql += ` ORDER BY assignments.id DESC LIMIT ? OFFSET ?`;

    // 添加分页参数
    params.push(page_size);
    params.push((page - 1) * page_size);

    // 计算总数的SQL语句
    let countSql = `SELECT COUNT(*) as count FROM assignments
                JOIN classes ON assignments.class_id = classes.id 
                JOIN programs ON classes.program_id = programs.id
                WHERE ${conditions.join(' AND ')}`;

    // 执行查询
    const [assignmentsResult, countResult] = await Promise.all([
        query(listSql, params), // 查询作业列表
        query(countSql, params) // 查询总数
    ]);
    return {
        assignments: assignmentsResult, // 作业列表
        total: countResult // 总数
    };
}

// 文件上传
const uploads = (new_file_name, id,) => {
    let sql = `
        UPDATE assignments SET picture = ? 
        WHERE id = ?
    `
    return query(sql, [new_file_name, id])
}

// 文件删除
const deleteByPath = (id) => {
    let sql = `
        UPDATE assignments SET picture = null
        WHERE id = ?
    `
    return query(sql, [id])
}

// 作业的批改中的参数：assignment_id,student_id,status,submitted_at,score,comment,
// 作业批改--of--学生列表的显示
const student_list = async (assignment_id, class_id, name, page_size, page) => {
    // 查询学生列表及作业提交信息的 SQL
    let studentsSql = `
        SELECT 
            s.id, 
            s.name, 
            s.student_id,
            a_s.status,
            a_s.score,
            a_s.comment
        FROM students as s
        LEFT JOIN assignment_submissions as a_s 
            ON s.id = a_s.student_id 
            AND a_s.assignment_id = ?  -- 关联指定作业的提交记录
        WHERE s.class_id = ? 
        AND s.state_del = 0
    `;

    // 初始化参数数组（注意顺序：assignment_id 作为第一个参数）
    let params = [assignment_id, class_id];

    let conditions = [];
    if (name) {
        conditions.push(`s.name LIKE ?`);
        params.push(`%${name}%`);
    }

    // 添加动态条件
    if (conditions.length > 0) {
        studentsSql += ` AND ${conditions.join(' AND ')}`;
    }

    // 分页处理
    studentsSql += ` LIMIT ? OFFSET ?`;
    params.push(page_size);
    params.push((page - 1) * page_size);

    // 计算总数的 SQL（只需统计学生表）
    let countSql = `
        SELECT COUNT(*) as count 
        FROM students 
        WHERE class_id = ? 
        AND state_del = 0
    `;
    let countParams = [class_id];
    if (name) {
        countSql += ` AND name LIKE ?`;
        countParams.push(`%${name}%`);
    }

    try {
        const [studentsResult, countResult] = await Promise.all([
            query(studentsSql, params),
            query(countSql, countParams)
        ]);

        // 处理结果：确保字段值为 NULL 时转为默认值
        const formattedStudents = studentsResult.map(student => ({
            id: student.id,
            name: student.name,
            student_id: student.student_id,
            status: student.status || '未批改',  // 无提交记录时显示默认状态
            score: student.score || 0,
            comment: student.comment || ''
        }));

        return {
            students: formattedStudents,
            total: countResult[0].count
        };
    } catch (error) {
        console.error('数据获取失败:', error);
        throw error;
    }
};

// 作业批改，给对应的学生的对应作业评分
const correct = async (assignment_id, student_id, status, submitted_at, score, comment, create_time) => {
    // 检查是否已经存在记录
    const checkSql = 'SELECT * FROM assignment_submissions WHERE assignment_id = ? AND student_id = ?';
    const [existingSubmission] = await query(checkSql, [assignment_id, student_id]);

    // 构建SQL语句和参数
    let sql;
    let params;
    if (existingSubmission) {
        // 如果存在记录，则更新
        sql = `
            UPDATE assignment_submissions
            SET status = ?, submitted_at = ?, score = ?, comment = ?
            WHERE assignment_id = ? AND student_id = ?
        `;
        params = [status, submitted_at, score, comment, assignment_id, student_id];
    } else {
        // 如果不存在记录，则插入
        sql = `
            INSERT INTO assignment_submissions (assignment_id, student_id, status, submitted_at, score, comment, create_time)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        `;
        params = [assignment_id, student_id, status, submitted_at, score, comment, create_time];
    }
    return query(sql, params)
};

// 作业分析--表格
const homework_table = async (name, classes_id, programs_id, page, page_size) => {
    // 构建基础查询语句
    let studentsSql = `
        SELECT id, name, student_id, class_id FROM students
    `;

    // 如果提供了 classes_id 或 programs_id ，则添加到查询条件中
    // 动态构建 WHERE 条件
    let whereConditions = [];
    const params = [];

    // 处理 classes_id
    if (classes_id) {
        whereConditions.push(`class_id = ?`);
        params.push(classes_id);
    }

    // 处理 name
    if (name) {
        whereConditions.push(`name LIKE ?`);
        params.push(`%${name}%`);
    }

    // 处理 programs_id
    if (programs_id) {
        whereConditions.push(`class_id IN (SELECT id FROM classes WHERE program_id = ?)`);
        params.push(programs_id);
    }

    // 合并 WHERE 条件
    if (whereConditions.length > 0) {
        studentsSql += ` WHERE ${whereConditions.join(' AND ')}`;
    }


    let studentsResult = await query(studentsSql, params);

    // 存储每个学生的作业统计信息
    let studentHomeworkStats = {};

    // 遍历学生列表，为每个学生查询作业完成情况
    for (let student of studentsResult) {
        let student_id = student.student_id;
        let studentId = student.id;
        let class_id = student.class_id;

        // 查询专业名称
        let classSql = `
            SELECT classes.name, classes.id as classes_id, programs.name as program_name, programs.id AS programs_id
            FROM classes 
            JOIN programs ON classes.program_id = programs.id
            WHERE classes.id = ? -- 这里有条件限制值就会变得唯一
        `;
        let classResult = await query(classSql, [class_id]);
        let className = classResult.length > 0 ? classResult[0].name : '未知班级';
        let classes_id = classResult.length > 0 ? classResult[0].classes_id : '0';
        let programName = classResult.length > 0 ? classResult[0].program_name : '未知专业';
        let programs_id = classResult.length > 0 ? classResult[0].programs_id : '0';

        let statsSql = `
            SELECT status, COUNT(*) as count
            FROM assignment_submissions
            WHERE student_id = ? -- 这里有条件限制值就会变得唯一
            GROUP BY status
        `;
        let statsResult = await query(statsSql, [studentId]);

        // 统计完成、未完成、迟交的数量
        let completed = statsResult.find(stat => stat.status === '完成')?.count || 0;
        let notCompleted = statsResult.find(stat => stat.status === '未完成')?.count || 0;
        let late = statsResult.find(stat => stat.status === '迟交')?.count || 0;

        let totalSql = `
            SELECT COUNT(id) AS total_assignments 
            FROM assignments 
            WHERE class_id = ? -- 这里有条件限制值就会变得唯一
        `
        let totalResult = await query(totalSql, [classes_id])
        // 计算总作业数和完成率
        // let totalAssignments = completed + notCompleted + late;
        // 获取总作业数，若无结果则默认为0
        let totalAssignments = totalResult[0]?.total_assignments || 0;
        let completionRate = totalAssignments > 0 ? ((completed / totalAssignments) * 100).toFixed(2) + '%' : '0%';

        // 存储统计信息，包括专业名称
        studentHomeworkStats[studentId] = {
            studentName: student.name,
            studentId: student_id,
            className: className, // 班级名称
            classes_id: classes_id, // 班级id
            programName: programName, // 专业名称
            programs_id: programs_id, // 专业id 
            totalAssignments: totalAssignments,
            completed: completed,
            late: late,
            notCompleted: notCompleted,
            completionRate: completionRate
        };
    }

    // 分页处理
    let paginatedResults = Object.values(studentHomeworkStats).slice((page - 1) * page_size, page * page_size);
    // 计算列表总数
    let totalListCount = Object.values(studentHomeworkStats).length;

    return {
        list: paginatedResults,
        totalCount: totalListCount
    };
};

// 学生作业提交趋势表
const sub_trend = (title, class_id, program_id, start_time, end_time) => {
    // 使用 DATE_FORMAT 函数来格式化日期，只显示年-月-日
    let sql = `
        SELECT DATE_FORMAT(CONVERT_TZ(assignment_submissions.submitted_at, '+00:00', '+08:00'), '%Y-%m-%d') as day, 
               assignment_submissions.status, 
               COUNT(*) AS count
        FROM assignment_submissions
        JOIN students ON assignment_submissions.student_id = students.id
        JOIN assignments ON assignment_submissions.assignment_id = assignments.id
        JOIN classes ON students.class_id = classes.id
        JOIN programs ON classes.program_id = programs.id
    `;
    let params = [];
    let whereClauses = [];

    // 如果提供了title，则添加到查询条件中
    if (title) {
        whereClauses.push(`assignments.title LIKE ?`);
        params.push(`%${title}%`);
    }

    // 如果提供了class_id，则添加到查询条件中
    if (class_id) {
        whereClauses.push(`classes.id = ?`);
        params.push(class_id);
    }

    // 如果提供了program_id，则添加到查询条件中
    if (program_id) {
        whereClauses.push(`programs.id = ?`);
        params.push(program_id);
    }

    // 如果提供了start_time和end_time，则添加到查询条件中
    if (start_time && end_time) {
        whereClauses.push(`assignment_submissions.submitted_at BETWEEN ? AND ?`);
        params.push(start_time, end_time);
    }

    // 如果有where条件，则添加WHERE子句
    if (whereClauses.length > 0) {
        sql += ` WHERE ${whereClauses.join(' AND ')}`;
    }

    // 添加GROUP BY子句，按照日期和状态分组
    sql += ` GROUP BY DATE_FORMAT(CONVERT_TZ(assignment_submissions.submitted_at, '+00:00', '+08:00'), '%Y-%m-%d'), assignment_submissions.status`;

    // 执行查询
    return query(sql, params);
};
// 作业难度分布图
const difficulty = (class_id, program_id) => {
    let sql = `
        SELECT 
            classes.name AS class_name,   -- 班级名称
            assignments.difficulty,        -- 难度等级
            COUNT(*) AS count              -- 统计数量
        FROM assignments
        JOIN classes ON assignments.class_id = classes.id
        JOIN programs ON classes.program_id = programs.id
    `;
    let params = [];
    let whereClauses = [];

    // 动态添加过滤条件
    if (class_id) {
        whereClauses.push(`classes.id = ?`);
        params.push(class_id);
    }
    if (program_id) {
        whereClauses.push(`programs.id = ?`);
        params.push(program_id);
    }

    // 添加 WHERE 子句（如果有条件）
    if (whereClauses.length > 0) {
        sql += ` WHERE ${whereClauses.join(' AND ')}`;
    }

    // 关键修正：按班级名称和难度联合分组
    sql += ` GROUP BY classes.id, assignments.difficulty`;

    return query(sql, params);
};

// 获取全部作业的名字
const allwork = (class_id, program_id) => {
    let sql = `
        SELECT a.id, a.title
        FROM assignments a
        LEFT classes c ON a.calss_id = c.id
        LEFT programs p ON c.program_id = p.id
        WHERE 1 = 1
    `
    let params = []
    if (class_id) {
        sql += ` AND c.id = ?`
        params.push(class_id)
    }
    if (program_id) {
        sql += ` AND p.id = ?`
        params.push(program_id)
    }
    return query(sql, params)
}
module.exports = {
    add,
    del_f,
    start_state,
    del_t,
    get,
    set,
    list,
    uploads,
    student_list,
    correct,
    homework_table,
    sub_trend,
    difficulty,
    allwork
}